﻿using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Data;
using System.Reflection;
using Microsoft.EntityFrameworkCore;

namespace S.Authorization.Infrastructure
{
    public class DbHelper
    {
        private string ConnectionString = "";
        public DbHelper(IConfiguration configuration)
        {
            ConnectionString = configuration.GetValue<string>("ConnectionStrings");
        }
        /// <summary>
        /// 公共查询
        /// </summary>
        /// <param name="sqlcmd"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public async Task<DataSet> Search(string sqlcmd, SqlParameter[] values = null)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                try
                {
                    //打开连接
                    conn.Open();
                    //执行Sql命令对象,传入要执行的Sql语句和数据连接对象
                    SqlCommand cmmand = new SqlCommand(sqlcmd, conn);
                    if (values != null)
                    {
                        cmmand.Parameters.AddRange(values);
                    }
                    //数据适配器
                    SqlDataAdapter adapter = new SqlDataAdapter(cmmand);
                    //创建数据集
                    DataSet set = new DataSet();
                    //填充数据
                    adapter.Fill(set);
                    return set;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 返回首行首列
        /// </summary>
        /// <param name="sqlcmd"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public async Task<object> ExecuteScalar(string sqlcmd, SqlParameter[] values = null)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                try
                {
                    await conn.OpenAsync();
                    SqlCommand cmmand = new SqlCommand(sqlcmd, conn);
                    if (values != null)
                    {
                        cmmand.Parameters.AddRange(values);
                    }
                    object result = await cmmand.ExecuteScalarAsync();
                    return result;
                }
                catch (Exception ex)
                {
                    // 处理异常，例如记录日志、返回错误信息等
                    Console.WriteLine("执行数据库查询时发生异常：" + ex.Message);
                    throw;
                }
            }
        }
        /// <summary>
        /// 返回受影响行数
        /// </summary>
        /// <param name="sqlcmd"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public async Task<int> ExecuteNonQuery(string sqlcmd, SqlParameter[] values = null)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                try
                {
                    //打开连接
                    conn.Open();
                    //执行Sql命令对象,传入要执行的Sql语句和数据连接对象
                    SqlCommand cmmand = new SqlCommand(sqlcmd, conn);

                    if (values != null)
                    {
                        cmmand.Parameters.AddRange(values);
                    }
                    int v = await cmmand.ExecuteNonQueryAsync();

                    return v;
                }
                catch (Exception)
                {

                    throw;
                }
                finally
                {
                    conn.Close();
                }

            }

        }
        public List<T> ConvertDataTableList<T>(DataTable dataTable) where T : class, new()
        {
            //创建泛型集合
            List<T> list = new List<T>();
            //循环行
            foreach (DataRow row in dataTable.Rows)
            {
                //实例化集合
                T obj = new T();
                //循环列

                foreach (DataColumn col in dataTable.Columns)
                {
                    //获取列名
                    PropertyInfo propertyInfo = obj.GetType().GetProperty(col.ColumnName);
                    if (propertyInfo != null && row[col] != DBNull.Value)
                    {
                        //赋值
                        propertyInfo.SetValue(obj, row[col], null);
                    }

                }
                list.Add(obj);

            }
            return list;
        }
        public async Task<List<T>> GetListDataBySql<T>(string sqlcmd, SqlParameter[] values = null) where T : class, new()
        {
            DataSet set = await Search(sqlcmd, values);
            return ConvertDataTableList<T>(set.Tables[0]);
        }
        /// <summary>
        /// 事务操作
        /// </summary>
        /// <param name="sqlcmd"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public async Task<bool> ExecuteTransaction(List<string> sqlcmd, List<SqlParameter[]> values = null)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                // 开始事务
                SqlTransaction transaction = conn.BeginTransaction();
                try
                {
                    for (int i = 0; i < sqlcmd.Count(); i++)
                    {
                        SqlCommand sqlCommand = new SqlCommand(sqlcmd[i], conn, transaction);
                        if (values[i] != null)
                        {
                            sqlCommand.Parameters.AddRange(values[i]);
                        }
                        await sqlCommand.ExecuteNonQueryAsync();
                    }
                    await transaction.CommitAsync();
                    return true;
                }
                catch (Exception)
                {
                    //事务回滚
                    transaction.RollbackAsync();
                    throw;
                }
                finally { conn.Close(); }
            }

        }
    }
}
